

package com.tspp.dao;

import com.tspp.common.ResourceManager;
import com.tspp.dao.dto.ExamResult;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Concrete realization of examResultDao interface. Retrieve the connection from pool in every method
 * @author Maks
 */
public class ExamResultDaoImpl extends AbstractDao<ExamResult> implements ExamResultDao {
    /**
     * All finder methods in this class use this SELECT constant to build their queries
     */
    private static final String SQL_SELECT = "SELECT ID_EXAM, ID_ENTRANT, POINTS FROM " + getTableName() + "";
    /**
     * SQL INSERT statement for this table
     */
    private static final String SQL_INSERT = "INSERT INTO " + getTableName() + " ( ID_EXAM, ID_ENTRANT, POINTS ) VALUES ( ?, ?, ? )";
    /**
     * SQL UPDATE statement for this table
     */
    private static final String SQL_UPDATE = "UPDATE " + getTableName() + " SET POINTS = ? WHERE ID_EXAM = ? AND ID_ENTRANT = ?";
    /**
     * SQL DELETE statement for this table
     */
    private static final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE ID_EXAM = ? AND ID_ENTRANT = ?";
    /**
     * Index of column ID_EXAM
     */
    private static final int COLUMN_ID_EXAM = 1;
    /**
     * Index of column ID_ENTRANT
     */
    private static final int COLUMN_ID_ENTRANT = 2;
    /**
     * Index of column POINTS
     */
    private static final int COLUMN_POINTS = 3;

    /**
     * Inserts a new row in the EXAM_RESULT table.
     */
    public void insert(ExamResult dto) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ResourceManager.getConnection();
            stmt = conn.prepareStatement(SQL_INSERT);
            int index = 1;
            stmt.setInt(index++, dto.getIdExam());
            stmt.setInt(index++, dto.getIdEntrant());
            if (dto.getPoints() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getPoints());
            }
            //System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Updates a single row in the EXAM_RESULT table.
     */
    public void update(int exam, int entrant, ExamResult dto) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_UPDATE + " with DTO: " + dto);
            stmt = conn.prepareStatement(SQL_UPDATE);
            int index = 1;
            if (dto.getPoints() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getPoints());
            }
            stmt.setInt(index++, exam);
            stmt.setInt(index++, entrant);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }

    }

    /**
     * Deletes a single row in the EXAM_RESULT table.
     */
    public void delete(int exam, int entrant) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_DELETE + " with PK: " + pk);
            stmt = conn.prepareStatement(SQL_DELETE);
            stmt.setInt(1, exam);
            stmt.setInt(2, entrant);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Returns all rows from the EXAM_RESULT table that match the criteria 'ID_EXAM = :idExam AND ID_ENTRANT = :idEntrant'.
     */
    public ExamResult findByPrimaryKey(int idExam, int idEntrant) throws DaoException {
        List<ExamResult> ret = findByDynamicSelect(SQL_SELECT + " WHERE ID_EXAM = ? AND ID_ENTRANT = ?", new Object[]{new Integer(idExam), new Integer(idEntrant)});
        return ret.isEmpty() ? null : ret.get(0);
    }

    /**
     * Returns all rows from the EXAM_RESULT table that match the criteria ''.
     */
    public List<ExamResult> findAll() throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " ORDER BY ID_EXAM, ID_ENTRANT", null);
    }

    /**
     * Returns all rows from the EXAM_RESULT table that match the criteria 'ID_EXAM = :idExam'.
     */
    public List<ExamResult> findByExam(int idExam) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_EXAM = ?", new Object[]{new Integer(idExam)});
    }

    /**
     * Returns all rows from the EXAM_RESULT table that match the criteria 'ID_ENTRANT = :idEntrant'.
     */
    public List<ExamResult> findByEntrant(int idEntrant) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_ENTRANT = ?", new Object[]{new Integer(idEntrant)});
    }

    /**
     * Returns all rows from the EXAM_RESULT table that match the criteria 'POINTS = :points'.
     */
    public List<ExamResult> findWherePointsEquals(int points) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE POINTS = ? ORDER BY POINTS", new Object[]{new Integer(points)});
    }

     /**
     * Return table name this dao is operated with
     * @return String name of table
     */
    public static String getTableName() {
        return "EXAM_RESULT";
    }

    /**
     * Implements abstract getDtoFrom of AbstractDao
     * @see AbstractDAO
     * @param rs
     * @return newly created transfer object
     * @throws SQLException
     */
    @Override
    protected ExamResult getDtoFrom(ResultSet rs) throws SQLException {
        ExamResult dto = new ExamResult();
        dto.setIdExam(rs.getInt(COLUMN_ID_EXAM));
        dto.setIdEntrant(rs.getInt(COLUMN_ID_ENTRANT));
        dto.setPoints(rs.getInt(COLUMN_POINTS));
        if (rs.wasNull()) {
            dto.setPoints(-1);
        }
        return dto;
    }
}

